﻿Imports System.IO
Imports System.Text

Public Class frmExportCompta
    Private szSQL As String
    Private rs As ADODB.Recordset
    Private m_SortingColumn As ColumnHeader
    Private m_SortingIndex As Integer = 0

    Dim szJournalVente As String
    Dim szCodeSociete As String
    Dim szNomSociete As String
    Dim szCompteAux As String
    Dim szCompteTVA As String
    Dim nb As Integer = 0

    Private Sub frmExportCompta_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        InitComboSocietes(CB_SOCIETE)
        CB_SOCIETE.SelectedIndex = 0
        ' période = quinzaine
        TE_DATE_DEBUT.Value = Now.AddDays(-15)
        GB_GENERER.Visible = False
    End Sub

    Private Sub InitComptes(ByVal ix As Integer)
        Dim total_debit As Decimal = 0
        Dim total_credit As Decimal = 0

        nb = 0

        ' effacer la listview
        LV_COMPTE.Items.Clear()
        LV_COMPTE.ListViewItemSorter = Nothing

        ' Lire les écritures extraites
        szSQL = "CALL spGET_T_SYBEL('" & gUserCode & "','','','1953/05/17','','')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            LV_COMPTE.Items.Add(rs.Fields("journal").Value)
            LV_COMPTE.Items(nb).SubItems.Add(nv(rs.Fields("date").Value))
            LV_COMPTE.Items(nb).SubItems.Add(nv(rs.Fields("compte").Value))
            LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("libelle").Value)
            If rs.Fields("sens").Value = "C" Then
                LV_COMPTE.Items(nb).SubItems.Add(" ")
                LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("montant").Value)
                total_credit += rs.Fields("montant").Value
            Else
                LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("montant").Value)
                LV_COMPTE.Items(nb).SubItems.Add(" ")
                total_debit += rs.Fields("montant").Value
            End If
            LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("centre").Value)
            LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("no_ligne").Value)
            rs.MoveNext()
            nb = nb + 1
        Loop
        ' Close the recordset AND connection.
        rs.Close()
        TE_NB.Text = nb.ToString & " lignes extraites"
        If nb > 0 Then
            GB_GENERER.Visible = True
        Else
            GB_GENERER.Visible = False
        End If
        TE_NOM_FICHIER.Text = Mid(My.Application.Info.DirectoryPath, 1, 10) & "\EXPORT\" & szCodeSociete & "-AEM-" & _
                              Format(TE_DATE_DEBUT.Value, "yyMMdd") & ".PNM"
        TE_DEBIT.Text = total_debit.ToString
        TE_CREDIT.Text = total_credit.ToString
        ' restaurer l'ancienne ordre de tri de la listview
        If m_SortingIndex > 0 Then
            SortListView(LV_COMPTE, m_SortingColumn, m_SortingIndex)
        End If
        ' restaurer la position du curseur avant le init
        setIndice(ix, LV_COMPTE)

    End Sub

    Private Sub BO_GENERER_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_GENERER.Click
        ' Tampon String intermédiare
        Dim sb As New StringBuilder()
        Dim ligne1 As String
        Dim szmontant As String
        Dim montant As Decimal
        Dim sens As String

        TE_NOM_FICHIER.Text = Mid(My.Application.Info.DirectoryPath, 1, 10) & "\EXPORT\" & szCodeSociete & "-AEM-" & _
                              Format(TE_DATE_FIN.Value, "yyMMdd") & ".PNM"

        ' ecrire ligne d'entete
        ligne1 = szNomSociete
        sb.AppendLine(ligne1.ToString())

        ' Lire les écritures extraites
        szSQL = "CALL spGET_T_SYBEL('" & gUserCode & "','','','1953/05/17','','')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            ' si montant < 0, le rendre positif et inverser le sens
            montant = rs.Fields("montant").Value
            sens = rs.Fields("sens").Value
            If montant < 0 Then
                montant *= -1
                If sens = "C" Then
                    sens = "D"
                Else
                    sens = "C"
                End If
            End If

            ' nouvelle ligne
            ligne1 = Space(104) & "N"
            ' Code Journal
            Mid$(ligne1, 1, 3) = rs.Fields("journal").Value
            ' date de pièce
            Mid$(ligne1, 4, 6) = Format(rs.Fields("date").Value, "ddMMyy")
            ' type de pièce
            Mid$(ligne1, 10, 2) = "OD"
            ' compte général
            Mid$(ligne1, 12, 6) = rs.Fields("compte").Value
            ' reference
            Mid$(ligne1, 39, 8) = "AE" & Format(rs.Fields("date").Value, "ddMMyy")
            ' libelle
            Mid$(ligne1, 52, 25) = rs.Fields("libelle").Value
            ' date echeance
            Mid$(ligne1, 78, 6) = Format(rs.Fields("date").Value, "ddMMyy")
            ' sens
            Mid$(ligne1, 84, 1) = sens
            ' montant
            szmontant = FormatNumber(montant, 2, TriState.True, False, False)
            Mid$(ligne1, 85, 20) = Space(20 - szmontant.Length) & szmontant
            ' compte auxilliare ou analytique
            If Len(rs.Fields("centre").Value) > 0 Then
                ' compte auxilliaire ?
                If Mid(ligne1, 12, 3) = "411" Then
                    Mid$(ligne1, 25, 8) = "X" & rs.Fields("centre").Value
                Else
                    ' écrire la ligne generale
                    sb.AppendLine(ligne1.ToString())
                    ' puis préparer la ligne analytique
                    Mid$(ligne1, 25, 8) = "A" & rs.Fields("centre").Value
                End If
            End If
            ' ecrire la ligne 1
            sb.AppendLine(ligne1.ToString())

            rs.MoveNext()
            nb = nb + 1
        Loop
        ' Close the recordset AND connection.
        rs.Close()

        ' écrire le tampon dans le fichier de sortie
        Using sr As New StreamWriter(TE_NOM_FICHIER.Text, False, System.Text.Encoding.Default)
            sr.Write(sb.ToString())
        End Using
        MsgBox("La génération du fichier d'export" & vbCrLf & TE_NOM_FICHIER.Text & vbCrLf & "est terminée.", vbInformation)

    End Sub

    Private Sub TE_DATE_DEBUT_ValueChanged1(ByVal sender As Object, ByVal e As System.EventArgs) Handles TE_DATE_DEBUT.ValueChanged
        ' date de fin = date de début + 15 jours
        TE_DATE_FIN.Value = TE_DATE_DEBUT.Value.AddDays(15)
        TE_DATE_FIN.Value = TE_DATE_FIN.Value.AddDays(-1)
    End Sub

    Private Sub BO_EXTRAIRE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_EXTRAIRE.Click
        Dim rsAgence As ADODB.Recordset = Nothing
        Dim rsSybel As ADODB.Recordset = Nothing
        Dim ix As Integer
        Dim ix2 As Integer
        Dim nbAgences As Integer = 0
        Dim nOrigine As Integer
        Dim OldMontant As Decimal
        Dim dJour As Date
        Dim sDateValeur As String

        If DateDiff(DateInterval.Day, TE_DATE_DEBUT.Value, TE_DATE_FIN.Value) < 0 Then
            MsgBox("La date de fin doit être supérieure à la date début", MsgBoxStyle.Critical)
            Exit Sub
        End If

        ' RAZ la table T_SYBEL pour extraire les écritures comptable
        szSQL = "spDel_T_SYBEL('" & gUserCode & "')"
        ExecQuery(ctDSN_AEM, szSQL)

        Me.Cursor = Cursors.WaitCursor
        ' préparer les écritures de REGLEMENTS pour cumuler 
        ' par mode de reglement / agence / date écriture
        szSQL = "CALL spGet_P_TABLES_byCODE('P_MODE_REGL',0)"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            ' mode de réglement à comptabiliser ?
            If rs.Fields("code").Value <= 5 Then

                ' Lire les AGENCES
                nbAgences = 0
                szSQL = "CALL spGet_P_TABLES_byCODE('P_AGENCES',0)"
                GetRecordset(ctDSN_AEM, szSQL, rsAgence)
                Do While Not rsAgence.EOF                        'crer un écriture cumul par mode de réglement par agence (débit banque)
                    Application.DoEvents()
                    TE_NB.Text = "Initialiser " & rs.Fields("lib4").Value & " ag. " & nbAgences
                    nbAgences += 1
                    ix = rsAgence.Fields("code").Value
                    dJour = TE_DATE_DEBUT.Value
                    Do While dJour.Date <= TE_DATE_FIN.Value.Date
                        szSQL = "CALL spINS_T_ECRITURES('" & gUserCode & "','BNQ_D'," & ix & "," & rs.Fields("code").Value & ",0,'" & _
                        Format(dJour, "yyyy/MM/dd") & "','" & "REM " & rs.Fields("lib4").Value & " Ag:" & ix & "','" & rsAgence.Fields("cpt_bnq").Value & "','" & rsAgence.Fields("jnl_bnq").Value & "')"
                        ExecQuery(ctDSN_AEM, szSQL)
                        dJour = dJour.AddDays(1)
                    Loop
                    rsAgence.MoveNext()
                Loop
            End If
            rs.MoveNext()
        Loop
        rs.Close()
        rsAgence.Close()

        ' préparer les écritures VENTE et BNQ Clients pour cumuler 
        ' par taux de TVS / Agence / Agence d'origine de l'élève

        szSQL = "CALL spGet_P_TABLES_byCODE('P_TVA',0)"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            Application.DoEvents()
            TE_NB.Text = "Initialisation TVA " & rs.Fields("lib4").Value
            ' Lire les AGENCES
            szSQL = "CALL spGet_P_TABLES_byCODE('P_AGENCES',0)"
            GetRecordset(ctDSN_AEM, szSQL, rsAgence)
            Do While Not rsAgence.EOF
                'créer un écriture cumul par code tva par agence (débit ventes)
                ix = rsAgence.Fields("code").Value
                Application.DoEvents()
                TE_NB.Text = "Initialiser TVA " & rs.Fields("lib4").Value & " ag. " & ix

                szSQL = "CALL spINS_T_ECRITURES('" & gUserCode & "','VTE_D'," & ix & "," & rs.Fields("code").Value & ",0,'" & _
                 Format(TE_DATE_FIN.Value, "yyyy/MM/dd") & "','" & "VENTE " & rs.Fields("lib4").Value & " Ag:" & ix & "','C" & rs.Fields("lib4").Value & ix & "','" & rsAgence.Fields("jnl_bnq").Value & "')"
                ExecQuery(ctDSN_AEM, szSQL)

                For ix2 = 1 To nbAgences
                    dJour = TE_DATE_DEBUT.Value
                    Do While dJour.Date <= TE_DATE_FIN.Value.Date
                        'créer un écriture cumul par code tva par agence (crédit banque)
                        szSQL = "CALL spINS_T_ECRITURES('" & gUserCode & "','BNQ_C'," & ix & "," & rs.Fields("code").Value & "," & ix2 & ",'" & _
                        Format(dJour, "yyyy/MM/dd") & "','" & "REGLEM " & rs.Fields("lib4").Value & " Ag:" & ix & "','C" & rs.Fields("lib4").Value & ix2 & "','" & rsAgence.Fields("jnl_bnq").Value & "')"
                        ExecQuery(ctDSN_AEM, szSQL)
                        dJour = dJour.AddDays(1)
                    Loop
                Next
                rsAgence.MoveNext()
            Loop
            rs.MoveNext()
        Loop
        rs.Close()
        rsAgence.Close()

        Dim mtTVA_ENC As Decimal = 0
        Dim mtHT As Decimal = 0
        nb = 0
        ' Lire les comptes clients valorisés dans la période
        szSQL = "SELECT c.*, t.taux as ttva FROM eleves_cpt c INNER JOIN eleves e ON c.cd_cli = e.cd_cli INNER JOIN p_tva t ON c.ctva = t.code " & _
                "WHERE c.date_valeur BETWEEN '" & Format(TE_DATE_DEBUT.Value, "yyyy/MM/dd") & "' AND '" & Format(TE_DATE_FIN.Value, "yyyy/MM/dd") & _
                "' AND e.societe='" & szCodeSociete & "' AND (c.credit <> 0 OR c.mtval <> 0) AND e.permis_demande IN ('B','A','AL','A1','A2','AM','POINT')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            Application.DoEvents()
            ' origine de l'élève
            nOrigine = Mid(Format(rs.Fields("cd_cli").Value, "000000"), 1, 1)
            sDateValeur = Format(rs.Fields("date_valeur").Value, "yyyy/MM/dd")

            ' écriture de réglement 
            If rs.Fields("credit").Value <> 0 Then
                ' à comptabiliser ?
                If rs.Fields("mode_regl").Value <= 5 Then
                    szSQL = "CALL spUPD_T_ECRITURES('" & gUserCode & "','BNQ_D'," & rs.Fields("agence").Value & "," & rs.Fields("mode_regl").Value & ",0,'" & _
                        sDateValeur & "'," & rs.Fields("credit").Value & ")"
                    ExecQuery(ctDSN_AEM, szSQL)

                    ' TVA exo
                    szSQL = "CALL spUPD_T_ECRITURES('" & gUserCode & "','BNQ_C'," & rs.Fields("agence").Value & ",0," & nOrigine & ",'" & _
                        sDateValeur & "'," & rs.Fields("mt2").Value & ")"
                    ExecQuery(ctDSN_AEM, szSQL)
                    ' TVA 1 
                    szSQL = "CALL spUPD_T_ECRITURES('" & gUserCode & "','BNQ_C'," & rs.Fields("agence").Value & ",1," & nOrigine & ",'" & _
                        sDateValeur & "'," & rs.Fields("mt3").Value & ")"
                    ExecQuery(ctDSN_AEM, szSQL)
                    ' TVA 2
                    szSQL = "CALL spUPD_T_ECRITURES('" & gUserCode & "','BNQ_C'," & rs.Fields("agence").Value & ",2," & nOrigine & ",'" & _
                        sDateValeur & "'," & rs.Fields("mt1").Value & ")"
                    ExecQuery(ctDSN_AEM, szSQL)
                End If
            Else
                ' === cumuler les ecritures de VENTES si no compte saisi
                If nz(rs.Fields("compte").Value) > 0 Then
                    'cumuler ecriture de vente
                    szSQL = "CALL spUPD_T_ECRITURES('" & gUserCode & "','VTE_D'," & nOrigine & "," & rs.Fields("ctva").Value & ",0,'" & _
                        Format(TE_DATE_FIN.Value, "yyyy/MM/dd") & "'," & rs.Fields("mtval").Value & ")"
                    ExecQuery(ctDSN_AEM, szSQL)
                    ' lire l'agence pour obtenir son compte analytique
                    szSQL = "CALL spGet_P_TABLES_byCODE('P_AGENCES'," & rs.Fields("agence").Value & ")"
                    GetRecordset(ctDSN_AEM, szSQL, rsAgence)
                    If rsAgence.RecordCount < 1 Then
                        MsgBox("Agence no " & rs.Fields("agence").Value & " non trouvée.")
                        Exit Sub
                    End If
                    ' compte déja crée ?
                    szSQL = "CALL spGET_T_SYBEL('" & gUserCode & "','" & rs.Fields("societe").Value & "','" & szJournalVente & "','" & _
                            Format(TE_DATE_FIN.Value, "yyyy/MM/dd") & "','" & rs.Fields("compte").Value & "','" & rsAgence.Fields("centre").Value & "')"
                    GetRecordset(ctDSN_AEM, szSQL, rsSybel)
                    If rsSybel.RecordCount < 1 Then
                        ' non, creer
                        szSQL = "CALL spINS_T_SYBEL('" & gUserCode & "','" & rs.Fields("societe").Value & "','" & szJournalVente & "','" & _
                                Format(TE_DATE_FIN.Value, "yyyy/MM/dd") & "','" & rs.Fields("compte").Value & "','" & rsAgence.Fields("centre").Value & "','" & _
                                Mid(nq(rs.Fields("intitule").Value), 1, 25) & "',0,'C')"
                        ExecQuery(ctDSN_AEM, szSQL)
                        OldMontant = 0
                    Else
                        OldMontant = rsSybel.Fields("montant").Value
                    End If

                    ' cumuler le montant HT et cumuler le total TVA
                    mtHT = Math.Round(rs.Fields("mtval").Value / (1 + (rs.Fields("ttva").Value / 100)), 2)
                    mtTVA_ENC += rs.Fields("mtval").Value - mtHT

                    szSQL = "CALL spUPD_T_SYBEL('" & gUserCode & "','" & rs.Fields("societe").Value & "','" & szJournalVente & "','" & _
                            Format(TE_DATE_FIN.Value, "yyyy/MM/dd") & "','" & rs.Fields("compte").Value & "','" & rsAgence.Fields("centre").Value & "'," & _
                            OldMontant + mtHT & ")"
                    ExecQuery(ctDSN_AEM, szSQL)
                End If
                End If
                rs.MoveNext()
                nb = nb + 1
                TE_NB.Text = nb.ToString & " lignes lues"
        Loop
        rs.Close()
        ' rsAgence.Close()

        ' === Générer les lignes DEBIT des VENTES
        szSQL = "CALL spGET_T_ECRITURES('" & gUserCode & "','VTE_D')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            szSQL = "CALL spINS_T_SYBEL('" & gUserCode & "','" & szCodeSociete & "','" & szJournalVente & "','" & _
                    Format(rs.Fields("date").Value, "yyyy/MM/dd") & "','" & szCompteAux & "','" & rs.Fields("compte").Value & "','" & _
                    rs.Fields("libelle").Value & "'," & rs.Fields("montant").Value & ",'D')"
            ExecQuery(ctDSN_AEM, szSQL)
            rs.MoveNext()
        Loop
        rs.Close()

        ' === Générer les lignes CREDIT des BANQUES
        szSQL = "CALL spGET_T_ECRITURES('" & gUserCode & "','BNQ_C')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            szSQL = "CALL spINS_T_SYBEL('" & gUserCode & "','" & szCodeSociete & "','" & rs.Fields("journal").Value & "','" & _
                    Format(rs.Fields("date").Value, "yyyy/MM/dd") & "','" & szCompteAux & "','" & rs.Fields("compte").Value & "','" & _
                    rs.Fields("libelle").Value & "'," & rs.Fields("montant").Value & ",'C')"
            ExecQuery(ctDSN_AEM, szSQL)
            rs.MoveNext()
        Loop
        rs.Close()

        ' === Générer les lignes DEBIT des BANQUES
        szSQL = "CALL spGET_T_ECRITURES('" & gUserCode & "','BNQ_D')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            szSQL = "CALL spINS_T_SYBEL('" & gUserCode & "','" & szCodeSociete & "','" & rs.Fields("journal").Value & "','" & _
                    Format(rs.Fields("date").Value, "yyyy/MM/dd") & "','" & rs.Fields("compte").Value & "','','" & _
                    rs.Fields("libelle").Value & "'," & rs.Fields("montant").Value & ",'D')"
            ExecQuery(ctDSN_AEM, szSQL)
            rs.MoveNext()
        Loop
        rs.Close()

        ' === Générer la ligne TVA ENCAISSE
        szSQL = "CALL spINS_T_SYBEL('" & gUserCode & "','" & szCodeSociete & "','" & szJournalVente & "','" & _
                    Format(TE_DATE_FIN.Value, "yyyy/MM/dd") & "','" & szCompteTVA & "','','TVA COLLECTE'," & _
                    mtTVA_ENC & ",'C')"
        ExecQuery(ctDSN_AEM, szSQL)

        Me.Cursor = Cursors.Default

        InitComptes(0)

    End Sub

    Private Sub CB_SOCIETE_SelectedValueChanged1(ByVal sender As Object, ByVal e As System.EventArgs) Handles CB_SOCIETE.SelectedValueChanged
        ' lire la societe pour mémoriser ses paramètre
        szSQL = "CALL spGet_SOCIETES('" & Mid(CB_SOCIETE.SelectedItem, 1, 1) & " ')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            szCodeSociete = rs.Fields("code").Value
            szNomSociete = rs.Fields("nom").Value
            szCompteAux = rs.Fields("cpte_aux").Value
            szCompteTVA = rs.Fields("cpte_tva").Value
            szJournalVente = rs.Fields("jnl_vte").Value
            rs.Close()
        End If

    End Sub

    Private Sub LV_COMPTE_ColumnClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles LV_COMPTE.ColumnClick
        SortListView(LV_COMPTE, m_SortingColumn, e.Column)
    End Sub

    Private Sub LV_COMPTE_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles LV_COMPTE.DoubleClick
        If LV_COMPTE.SelectedItems.Count > 0 Then
            frmExportMAJ.pNo_ligne = LV_COMPTE.SelectedItems(0).SubItems(7).Text
            frmExportMAJ.ShowDialog()
            ' réaffiche la viewlist
            InitComptes(LV_COMPTE.SelectedItems(0).Index)
        End If

    End Sub

    Private Sub LV_COMPTE_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles LV_COMPTE.KeyDown
        If e.KeyCode = Keys.Enter Then
            ' touche Enter = DoubleClick
            LV_COMPTE_DoubleClick(sender, e)
        End If
    End Sub

End Class